package com.axdoc.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
 * 根据医患病种病症对应关系替换文件
 * @author jiangboliu
 *
 */
public class TagsUserDoctor20180124 {
	public static void main(String[] args) throws IOException {
		
	
		ApplicationContext ac = new FileSystemXmlApplicationContext("classpath:applicationContext.xml");
		JdbcTemplate jdbcTemplate=(JdbcTemplate) ac.getBean("jdbcTemplate");
		
		final Map<String,String> diseaseMap=new HashMap<String,String>();
		final Map<String,List<String>> map=new HashMap<String,List<String>>();
		jdbcTemplate.query("select disease_no,disease_name from disease", new RowMapper<Map<String,String>>() {

			@Override
			public Map<String, String> mapRow(ResultSet arg0, int arg1) throws SQLException {
				Map<String,String> map=new HashMap<String, String>();
				diseaseMap.put(arg0.getString("disease_no"), arg0.getString("disease_name"));
				return map;
			}
		});
		
		jdbcTemplate.query("select name,disease_no from illness", new RowMapper<Map<String,String>>() {

			@Override
			public Map<String, String> mapRow(ResultSet arg0, int arg1) throws SQLException {
				List<String> strs=map.get(arg0.getString("name"));
				if(strs==null) {
					strs=new ArrayList<String>();
				}
				strs.add(diseaseMap.get(arg0.getString("disease_no")));
				map.put(arg0.getString("name"), strs);
				return null;
			}
		});
		
		System.out.println(map);
		
		
		File daichuli = new File("/Users/jiangboliu/work/files/tags/userDoctorTags001.xls");
		InputStream dinputStream = new FileInputStream(daichuli);
		HSSFWorkbook dxssfWorkbook = new HSSFWorkbook(dinputStream);
		HSSFSheet dsheet1 = dxssfWorkbook.getSheetAt(0);
		
		for (Row row : dsheet1) {
			if(row.getPhysicalNumberOfCells()<2) {
				continue;
			}
			String str1 = null;
			if(row.getCell(0)==null){
				str1 = row.getCell(1).getStringCellValue();
			}else {
				String str = row.getCell(0).getStringCellValue();
				if(!StringUtils.isNotEmpty(str)) {
					str1 = row.getCell(1).getStringCellValue();
					row.getCell(0).setCellValue(convertStr(map.get(str1)));
				}
				
			}
			if(str1!=null) {
				row.createCell(0).setCellValue(convertStr(map.get(str1)));
			}
//			row.createCell(0).setCellValue(convertStr(map.get(str)));
//			if(row.getCell(1)!=null) {
//				if (row.getCell(4).getCellType() == Cell.CELL_TYPE_STRING) {
//					String str = row.getCell(4).getStringCellValue();
//					if(StringUtils.isNotEmpty(str)) {
//						
//						String[] strs=str.split("，");
//						for (String s : strs) {
//							if(map.get(s)!=null) {
//								sb.append(map.get(s)+"，");
//							}
//						}
//					}
//					
//					row.getCell(4).setCellValue(sb.toString());
//				}
//			}
//			
		}
		 FileOutputStream out = new FileOutputStream("/Users/jiangboliu/work/files/tags/userDoctorTags002.xls");
		 dxssfWorkbook.write(out);
	}
	
	public static String convertStr(List<String> strs) {
		if(strs==null) {
			return "";
		}
		StringBuffer sb=new StringBuffer();
		for (String string : strs) {
			sb.append(string+",");
		}
				
		return sb.subSequence(0, sb.length()-1).toString();
	}
}
